package com.example.membership.dao.impl;
import com.example.membership.dao.ProductDao;
import com.example.membership.model.Product;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductDaoImpl implements ProductDao {
    private final JdbcTemplate jdbcTemplate;

    public ProductDaoImpl(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public Product findById(int id) {
        String sql = "SELECT * FROM products WHERE id = ? FOR UPDATE";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, new int[]{Types.INTEGER}, productRowMapper());
        } catch (EmptyResultDataAccessException e) {
            throw new RuntimeException("商品不存在，ID: " + id);
        }
    }

    @Override
    public List<Product> findAll(String category, String sortBy, int page, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT * FROM products");
        List<Object> params = new ArrayList<>();
        List<Integer> paramTypes = new ArrayList<>();

        if (category != null && !category.isEmpty()) {
            sql.append(" WHERE category = ?");
            params.add(category.trim());
            paramTypes.add(Types.VARCHAR);
        }
        if (sortBy != null && !sortBy.isEmpty()) {
            sql.append(" ORDER BY ").append(sortBy.trim()).append(" ASC");
        }
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        paramTypes.add(Types.INTEGER);
        params.add((page - 1) * pageSize);
        paramTypes.add(Types.INTEGER);

        return jdbcTemplate.query(sql.toString(), params.toArray(), paramTypes.stream().mapToInt(i -> i).toArray(), productRowMapper());
    }

    @Override
    public int countAll(String category) {
        String sql = "SELECT COUNT(*) FROM products";
        if (category != null && !category.isEmpty()) {
            sql += " WHERE category = '" + category.trim() + "'";
        }
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public void insert(Product product) {
        String sql = "INSERT INTO products (name, category, price, sales_count, description, image_url, stock) VALUES (?, ?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql, product.getName(), product.getCategory(), product.getPrice(), product.getSales_count(), product.getDescription(), product.getImageUrl(), product.getStock());
    }

    @Override
    public void update(Product product) {
        String sql = "UPDATE products SET name = ?, category = ?, price = ?, sales_count = ?, description = ?, image_url = ?, stock = ? WHERE id = ?";
        jdbcTemplate.update(sql, product.getName(), product.getCategory(), product.getPrice(), product.getSales_count(), product.getDescription(), product.getImageUrl(), product.getStock(), product.getId());
    }

    @Override
    public void delete(int id) {
        String sql = "DELETE FROM products WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }

    private RowMapper<Product> productRowMapper() {
        return (rs, rowNum) -> {
            Product product = new Product();
            product.setId(rs.getInt("id"));
            product.setName(rs.getString("name"));
            product.setCategory(rs.getString("category"));
            product.setPrice(rs.getInt("price"));
            product.setSales_count(rs.getInt("sales_count"));
            product.setDescription(rs.getString("description"));
            product.setImageUrl(rs.getString("image_url"));
            product.setStock(rs.getInt("stock"));
            return product;
        };
    }
}